package com.smartleanx.module.hrm.dal.mysql.programme;

import com.alibaba.fastjson2.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.smartleanx.module.hrm.dal.dataobject.programme.HrmProgrammeEmployeeRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author mybatis-plus
 * @since 2024-12-03
 */
@Mapper
public interface HrmProgrammeEmployeeRecordMapper extends BaseMapper<HrmProgrammeEmployeeRecord> {


    @Select("select sum(employee_accumulation_amount)+sum(employee_programme_amount)+sum(programme_amount)+sum(accumulation_amount) " +
            " from hrm_programme_employee_record where programme_start_time=#{month} ")
    BigDecimal selectHJAmount(String month);

    @Select("select sum(programme_amount)+sum(accumulation_amount) " +
            " from hrm_programme_employee_record where programme_start_time=#{month} ")
    BigDecimal selectCompanyHJAmount(String month);


    @Select("select  programme_start_time monthTime, company_id companyId, (sum(employee_accumulation_amount)+sum(employee_programme_amount)+sum(programme_amount)+sum(accumulation_amount))hjAmount,(sum(programme_amount)+sum(accumulation_amount) )CompanyHJAmount,(sum(employee_accumulation_amount)+sum(employee_programme_amount))grHJAmount ,count(distinct employee_id) employeeCount" +
            " from hrm_programme_employee_record where programme_start_time >= #{yearStartTime} and programme_start_time <= #{yearEndTime} group by programme_start_time")
    List<JSONObject> selectGRHJAmount(@Param("yearStartTime") String yearStartTime, @Param("yearEndTime") String yearEndTime);

    @Select("select hpc.company_name companyName, hper.company_id companyId, (sum(hper.employee_accumulation_amount)+sum(hper.employee_programme_amount)+sum(hper.programme_amount)+sum(hper.accumulation_amount))hjAmount,(sum(hper.programme_amount)+sum(hper.accumulation_amount) )CompanyHJAmount,(sum(hper.employee_accumulation_amount)+sum(hper.employee_programme_amount))grHJAmount ,count(distinct hper.employee_id) employeeCount" +
            " from hrm_programme_employee_record hper " +
            " left join hrm_programme_company hpc  on  hpc.id = company_id" +
            " where hper.programme_start_time=#{month} " +
            "group by hper.company_id")
    List<JSONObject> selectZDByCompany(@Param("month") String month, @Param("companyName") String companyName);


    @Select("select hper.employee_id employeeId,he.`name` employeeName,he.dept_id departmentId, (sum(hper.employee_accumulation_amount)+sum(hper.employee_programme_amount)+sum(hper.programme_amount)+sum(hper.accumulation_amount))hjAmount,(sum(hper.programme_amount)+sum(hper.accumulation_amount) )CompanyHJAmount,(sum(hper.employee_accumulation_amount)+sum(hper.employee_programme_amount))grHJAmount ,count(distinct hper.employee_id) employeeCount" +
            " from hrm_programme_employee_record hper" +
            " left join hrm_employee he on he.id=employee_id" +
            " where hper.programme_start_time=#{month}" +
            "" +
            " group by hper.employee_id")
    List<JSONObject> selectZDByEmployee(@Param("month") String month, @Param("name") String name);



    @Select("select hper.employee_id employeeId,he.`name` employeeName,he.dept_id departmentId, (sum(hper.employee_accumulation_amount)+sum(hper.employee_programme_amount)+sum(hper.programme_amount)+sum(hper.accumulation_amount))hjAmount,(sum(hper.programme_amount)+sum(hper.accumulation_amount) )CompanyHJAmount,(sum(hper.employee_accumulation_amount)+sum(hper.employee_programme_amount))grHJAmount ,count(distinct hper.employee_id) employeeCount" +
            " from hrm_programme_employee_record hper" +
            " left join hrm_employee he on he.id=employee_id" +
            " where hper.programme_start_time=#{month} and hper.company_id=#{companyId}" +
            "" +
            " group by hper.employee_id")
    List<JSONObject> selectZDByEmployeeAndCompany(@Param("month") String month,@Param("companyId") Integer companyId);

}
